--1
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET, 
IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'REGION' ROLLUP_TYPE,  
REGION,
PRODUCT, 
LOCATION ,
DWELLING
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.DIRECTION, B.START_DT, 
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE(C.REGION ,'ALL') REGION
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT AND A.MN = '{0}'
GROUP	BY CUBE  (REGION, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY REGION, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;
--2
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET, IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'MARKET' ROLLUP_TYPE, B.MARKET, 
PRODUCT, 
LOCATION ,
DWELLING
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	 B.DIRECTION, B.START_DT,
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE( C.MARKET,'ALL') MARKET		
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT AND A.MN = '{0}' 
GROUP	BY CUBE  (MARKET, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY MARKET, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;
--3
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET, IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'AREA_MGR' ROLLUP_TYPE,B.AREA_MGR, 
PRODUCT, 
LOCATION ,
DWELLING 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.DIRECTION, B.START_DT,
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE( C.AREA_MGR,'ALL') AREA_MGR		
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT AND A.MN = '{0}' 
GROUP	BY CUBE  (AREA_MGR, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY AREA_MGR, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;
--4
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET, IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'STATE' ROLLUP_TYPE, B.STATE, 
PRODUCT, 
LOCATION ,
DWELLING
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	 B.DIRECTION, B.START_DT,
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE( SUBSTR(B.TRAC_ID,1,2) ,'ALL') STATE		
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		B 
	ON	A.DT=B.START_DT AND A.MN = '{0}' 
GROUP	BY CUBE  (STATE, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY STATE, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;
--5
SELECT  A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET, IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'ACCT_MGR' ROLLUP_TYPE, D.ACCT_MGR, 
PRODUCT, 
LOCATION ,
DWELLING 
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.DIRECTION, B.START_DT, 
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE( C.ACCT_MGR,'ALL')  ACCT_MGR		
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		D 
	ON	A.DT=D.START_DT AND A.MN = '{0}' 
GROUP	BY CUBE  (ACCT_MGR, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY ACCT_MGR, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;
;
--6
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET, IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'PORTFOLIO' ROLLUP_TYPE, D.PORTFOLIO, 
PRODUCT, 
LOCATION ,
DWELLING  
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.DIRECTION, B.START_DT, 
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL') PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE( C.PORTFOLIO,'ALL')  PORTFOLIO		
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON	C.TRAC_ID=B.TRAC_ID 
		) 
		D 
	ON	A.DT=D.START_DT  AND A.MN = '{0}'
GROUP	BY ROLLUP  (PORTFOLIO, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY PORTFOLIO, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;
--7
SELECT A.YR, A.MN, EXTRACT(DAY FROM A.DT) DY,
SUM(CASE	WHEN D.PRODUCT='IPTV' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) IPTV_IN,  
SUM(CASE	WHEN D.PRODUCT='IPTV' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) IPTV_OUT,  
IPTV_IN-IPTV_OUT IPTV_NET,  
SUM(CASE	WHEN D.PRODUCT='HSIA' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) HSIA_IN,  
SUM(CASE	WHEN D.PRODUCT='HSIA' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) HSIA_OUT,  
HSIA_IN-HSIA_OUT HSIA_NET,  
SUM(CASE	WHEN D.PRODUCT='VOIP' 	AND	DIRECTION='IN' THEN 1 ELSE	0 END	) VOIP_IN, 
SUM(CASE	WHEN D.PRODUCT='VOIP' 	AND	DIRECTION='OUT' THEN 1 ELSE	0 END	) VOIP_OUT,  
VOIP_IN-VOIP_OUT VOIP_NET,IPTV_NET + HSIA_NET + VOIP_NET TOT_NET,
'TRAC_ID' ROLLUP_TYPE,  D.TRAC_ID TRAC_ID,
D.PRODUCT, 
LOCATION ,
DWELLING   
FROM	
	MIS.ACC_CAL2 
	A 
	LEFT OUTER JOIN 
		(  SELECT	B.DIRECTION, B.START_DT, 
		CASE UPPER(B.PROGRAM)  WHEN 'APT' THEN 'MDU' WHEN 'CONDO' THEN 'MDU' ELSE 'SFU' END DWELLING,
		COALESCE(B.PRODUCT, 'ALL')  PRODUCT, 
		COALESCE(CASE B.NATIONAL_ID WHEN 'Y' THEN 'NATIONAL'  WHEN 'N' THEN 'LOCAL' END, 'BOTH') LOCATION , 
		COALESCE( B.TRAC_ID,'ALL')  TRAC_ID		
			FROM	MIS.ACC_RAW 
			B  
			INNER JOIN 
					MIS.ACC_BASE_DATA 
					C 
					ON C.TRAC_ID=B.TRAC_ID	 
		) 
		D 
	ON	A.DT=D.START_DT  AND A.MN = '{0}'
GROUP	BY ROLLUP  (TRAC_ID, PRODUCT, LOCATION, DWELLING), YR, MN, DY 
ORDER BY TRAC_ID, PRODUCT, LOCATION,  DWELLING, YR, MN, DY 
;